1. What is Instagram?

Instagram is a photo and video-sharing social networking service. It serves as a visual platform for users to capture, edit, and share moments with their followers and the wider public. For end customers, Instagram provides a curated feed of visual content, ephemeral stories, direct messaging, live broadcasting, and short-form video discovery (Reels). It also functions as a powerful marketing and discovery tool for creators and businesses, connecting them directly with highly engaged audiences through visual storytelling and algorithmic recommendations.

2. Requirement Analysis

Core Features

• User Management: Profile creation, editing, privacy settings (public/private accounts), and user verification.
• Social Graph: Follow/unfollow mechanisms (asymmetric relationship), follower requests for private accounts, and blocking.
• Media Feed: Uploading photos/videos, applying filters, writing captions, tagging users, and adding location data.
• Interactions: Liking, commenting, saving posts, and sharing posts to stories or via direct messages.
• Stories & Reels: Ephemeral 24-hour content (Stories) and short-form algorithmic video feeds (Reels).
• Direct Messaging (DM): 1-on-1 and group text, media, and voice messaging.
• Search & Explore: Discovering trending content, searching for users, hashtags, and locations.

Key Entities & Attributes

To support a Meta/Google-level scale, the entities need to be comprehensive:

1. User:

   • user_id (PK)
   • username (Unique)
   • email (Unique)
   • password_hash
   • full_name
   • bio
   • profile_picture_url
   • website_url
   • is_private (Boolean)
   • is_verified (Boolean)
   • created_at, updated_at

2. User_Follow (Social Graph):

   • follower_id (FK to User)
   • followee_id (FK to User)
   • status (Enum: Pending, Accepted, Blocked)
   • created_at
   • (Composite PK: follower_id, followee_id)

3. Post:

   • post_id (PK)
   • user_id (FK to User)
   • caption (Text)
   • location_id (FK to Location)
   • post_type (Enum: Image, Video, Carousel)
   • created_at, updated_at

4. Media (Assets for a Post):

   • media_id (PK)
   • post_id (FK to Post)
   • media_url
   • media_type (Enum: Image, Video)
   • sequence_order (For Carousels)
   • filter_applied
   • created_at

5. Post_Like:

   • post_id (FK to Post)
   • user_id (FK to User)
   • created_at
   • (Composite PK: post_id, user_id)

6. Comment:

   • comment_id (PK)
   • post_id (FK to Post)
   • user_id (FK to User)
   • parent_comment_id (Self-referencing FK for replies)
   • content (Text)
   • created_at, updated_at

7. Hashtag & Post_Hashtag:

   • Hashtag: hashtag_id (PK), name (Unique), created_at
   • Post_Hashtag: post_id (FK), hashtag_id (FK)

8. Story:

   • story_id (PK)
   • user_id (FK to User)
   • media_url
   • expires_at (Timestamp, 24 hours from creation)
   • created_at

3. Scale and Performance Considerations

• Read-Heavy Workload: The ratio of reading the feed to writing a new post is roughly 100:1. The system must optimize for fast reads.
• Feed Generation: Generating a user's timeline on-the-fly for users with thousands of followees is too slow. A hybrid approach is required:
  • Push Model (Fan-out on write): For regular users, when they post, the post ID is pushed to the in-memory timeline caches (e.g., Redis) of all their followers.
  • Pull Model (Fan-out on read): For celebrities (millions of followers), pushing is too expensive. Followers pull the celebrity's posts and merge them with their timeline at read time.
• Media Storage: CDNs (Content Delivery Networks) are essential for serving static media assets globally with low latency.
• Database Sharding: The user and post tables will outgrow a single machine. Sharding by user_id is a common strategy to distribute the load and storage.

4. Extended Entity-Relationship Model

erDiagram
    USER ||--o{ POST : "creates"
    USER ||--o{ COMMENT : "writes"
    USER ||--o{ POST_LIKE : "likes"
    USER ||--o{ USER_FOLLOW : "follows"
    USER ||--o{ STORY : "publishes"
    POST ||--o{ MEDIA : "contains"
    POST ||--o{ COMMENT : "has"
    POST ||--o{ POST_LIKE : "receives"
    POST ||--o{ POST_HASHTAG : "tagged with"
    HASHTAG ||--o{ POST_HASHTAG : "included in"
    COMMENT ||--o{ COMMENT : "replies to"

    USER {
        uuid user_id PK
        string username
        string email
        string password_hash
        boolean is_private
        boolean is_verified
    }
    POST {
        uuid post_id PK
        uuid user_id FK
        text caption
        enum post_type
        timestamp created_at
    }
    MEDIA {
        uuid media_id PK
        uuid post_id FK
        string media_url
        int sequence_order
    }
    USER_FOLLOW {
        uuid follower_id PK, FK
        uuid followee_id PK, FK
        enum status
    }
    COMMENT {
        uuid comment_id PK
        uuid post_id FK
        uuid user_id FK
        uuid parent_comment_id FK
        text content
    }

This content is reserved for Premium Members.